﻿#region Copyright (c) 2011, Ares Alexander Nicolaou , All rights reserved.
//Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

//    Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
//    Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

//THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#endregion

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;

namespace System.DbSizeForecaster
{
    public class MainWindowPresenter
    {
        readonly IMainWindowView _view;
        readonly ProjectData _projectData;
        double _reserveForIndexes;
        bool _projectLoaded;

        public IMainWindowView View { get { return _view; } }

        public MainWindowPresenter(IMainWindowView view)
        {
            if (view == null)
                throw new ArgumentNullException("view");

            _view = view;

            _projectData = new ProjectData();
        }

        internal void SelectDatabase(string sqlConnectionString)
        {
            //display connection string
            View.SelectedConnectionString = sqlConnectionString;

            //reset database
            _projectData.Project.Clear();
            _projectData.ProjectDetails.Clear();

            var projectRow = _projectData.Project.AddProjectRow(sqlConnectionString, DateTime.Now,View.UseMSSpaceUsed,View.PercentageReserveForDataAndIndexes.Value);

            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    string sql = "select [name] from sysobjects where xtype = 'u'";

                    SqlDataAdapter da = new SqlDataAdapter(sql,con);
                    DataTable dbTables = new DataTable();
                    da.Fill(dbTables);

                    con.Open();

                    foreach (DataRow dr in dbTables.Rows)
                    {
                        string tableName = dr["Name"].ToString();

                        sql = string.Format("Select Count(*) From [{0}]", tableName);

                        int existingRows = Convert.ToInt32(new SqlCommand(sql, con).ExecuteScalar());

                        //initialize row
                        _projectData.ProjectDetails.AddProjectDetailsRow(
                            projectRow,
                            tableName,
                            existingRows,
                            0,//forecasted row size
                            0,//avg row size
                            0);
                    }
                }

                _projectLoaded = true;

                UpdateAvgEntitySize();
            }
            catch (SqlException sex)
            {
                string msg = string.Format("Error connecting to the Sql Server database. Please check the connection settings and try again. The error message is: {0}.", sex.Message);
                View.ShowErrorMessage(msg);
            }
        }

        public void UpdateAvgEntitySize()
        {
            if (!_projectLoaded)
                return;

            string sqlConnectionString = View.SelectedConnectionString;

            using (SqlConnection con = new SqlConnection(sqlConnectionString))
            {
                string sql = "select [name] from sysobjects where xtype = 'u'";
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                DataTable dbTables = new DataTable();
                da.Fill(dbTables);

                #region sp space used , used to compare forecasted data useage with actual
                DataTable dtSpaceUsed = new DataTable();

                if (View.UseMSSpaceUsed)
                {
                    foreach (DataRow dr in dbTables.Rows)
                    {
                        sql = string.Format("sp_spaceused {0}", dr["Name"]);
                        da = new SqlDataAdapter(sql, con);
                        da.Fill(dtSpaceUsed);
                    }
                }
                #endregion

                con.Open();

                foreach (DataRow dr in dbTables.Rows)
                {
                    string tableName = dr["Name"].ToString();

                    sql = string.Format("Select Count(*) From [{0}]", tableName);

                    int existingRows = Convert.ToInt32(new SqlCommand(sql, con).ExecuteScalar());

                    //estimate row size based on data types 
                    double rowSizeEstimateKB = 0.00;

                    //estimate table size
                    double estimatedTableSizeKB = 0.00;

                    if (View.UseMSSpaceUsed && existingRows > 0)
                    {
                        //these statistics are only useful if there are existing rows in the table
                        //ideally more than 100 rows
                        DataRow drSpaceUsed = dtSpaceUsed.Select(string.Format("Name = '{0}'", tableName)).FirstOrDefault();

                        //parse data from sp_spaceused
                        estimatedTableSizeKB = Convert.ToDouble(drSpaceUsed["Reserved"].ToString().Replace(" KB", ""));
                        estimatedTableSizeKB += Convert.ToDouble(drSpaceUsed["Data"].ToString().Replace(" KB", ""));
                        estimatedTableSizeKB += Convert.ToDouble(drSpaceUsed["Index_Size"].ToString().Replace(" KB", ""));
                        estimatedTableSizeKB += Convert.ToDouble(drSpaceUsed["Unused"].ToString().Replace(" KB", ""));

                        rowSizeEstimateKB = estimatedTableSizeKB / existingRows;
                    }
                    else
                    {
                        //estimate row size based on data types 
                        rowSizeEstimateKB = GetEstimatedRowSize(tableName, con) / 1024.00;

                        //add a reserve for indexes/primary key
                        //this is usually thirty percent of the data on a given row with primary keys etc
                        if (!View.PercentageReserveForDataAndIndexes.HasValue)
                            View.PercentageReserveForDataAndIndexes = 30;

                        rowSizeEstimateKB = rowSizeEstimateKB * (1 + View.PercentageReserveForDataAndIndexes.Value / 100);

                        //estimate table size
                        estimatedTableSizeKB = rowSizeEstimateKB * existingRows;
                    }

                    ProjectData.ProjectDetailsRow detailsRow = _projectData.ProjectDetails.Select(string.Format("[TableName] = '{0}'", tableName)).FirstOrDefault() as ProjectData.ProjectDetailsRow;

                    if(detailsRow != null)
                        detailsRow.EntitySize = rowSizeEstimateKB;
                }
            }

            UpdateTableAndDbSize();
        }

        /// <summary>
        ///     Estimates the size of a row based on the average size of the 
        ///     data types of the field's in the row.
        /// </summary>
        /// <param name="tableName">The table to query.</param>
        /// <param name="transactions">The number of new transactions forecasted.</param>
        /// <param name="con"> The connection to use to query for the table's column definitions.</param>
        /// <returns>The row size in bytes.</returns>
        double GetEstimatedRowSize(string tableName, SqlConnection con)
        {
            DataTable dt = GetTableColumnDefinitions(tableName, con);

            double rowSize = 0.0;

            foreach (DataRow dr in dt.Rows)
            {
                string dataType = dr["datatype"].ToString();
                //this appears to be the number of bytes
                int length = Convert.ToInt32(dr["length"]);
                int scale = 0;
                if (dr["xscale"] != DBNull.Value)
                    scale += Convert.ToInt32(dr["xscale"]);

                int precision = length;
                if(dr["prec"] != DBNull.Value)
                    precision = Convert.ToInt32(dr["prec"]);

                double fieldBytes = DbFieldSizeProvider.GetFieldBytes(dataType, precision, scale, length);

                //field bytes should be close to the length value

                rowSize += fieldBytes; 
            }

            return rowSize;
        }

        /// <summary>
        ///     Gets the schema definition of a given table.
        /// </summary>
        /// <param name="tableName">The table to query.</param>
        /// <param name="con">The sql connection to use.</param>
        /// <returns>A data table containing the columns in the table, the column length, precision and scale.</returns>
        DataTable GetTableColumnDefinitions(string tableName, SqlConnection con)
        {
            string sql = "select name, prec, xscale, (select top 1 a.name from sys.systypes as a where a.xtype = syscolumns.xtype) as datatype,";
            sql += string.Format(" length from syscolumns where id = (select id from sysobjects where name = '{0}')", tableName);

            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(dt);

            return dt;
        }

        internal void UpdateTableAndDbSize()
        {
            double totalDbSizeKB = 0;

            foreach (var row in _projectData.ProjectDetails)
            {
                double tableSizeInKB = (((double)row.ExistingEntitiesCount + (double)row.ForecastedEntitiesCount) * row.EntitySize);

                row.TotalTableSize = tableSizeInKB;

                totalDbSizeKB += row.TotalTableSize;
            }

            View.ShowTableSizes(_projectData);

            View.TotalDatabaseSize = totalDbSizeKB / 1024.00;
        }

        internal void RemoveSelectedRow()
        {
            object selectedObject = View.SelectedRow;

            if (selectedObject == null)
            {
                string msg = "Please select the item to remove.";
                View.ShowUserMessage(msg);

                return;
            }

            DataRowView rowView = selectedObject as DataRowView;
            ProjectData.ProjectDetailsRow detailsRow = rowView.Row as ProjectData.ProjectDetailsRow;
            if (detailsRow == null)
                throw new InvalidOperationException("detailsRow is of the wrong type.");

            ProjectData.ProjectDetailsRow existingRow = _projectData.ProjectDetails.FindById(detailsRow.Id);

            if (existingRow != null)
                _projectData.ProjectDetails.RemoveProjectDetailsRow(existingRow);
        }

        internal void OpenProject(string filePath)
        {
            _projectData.Clear();

            _projectData.ReadXml(filePath);

            ProjectData.ProjectRow row = _projectData.Project.FirstOrDefault();
            if (row != null)
            {
                UpdateTableAndDbSize();

                View.SelectedConnectionString = row.ConnectionString;
                View.UseMSSpaceUsed = row.UseMSSpaceUsed;
                View.PercentageReserveForDataAndIndexes = row.PercentageReserveForIndexes;
            }

            _projectLoaded = true;
        }

        internal void SaveProject(string filePath)
        {
            if (File.Exists(filePath))
                File.Delete(filePath);

            _projectData.WriteXml(filePath);

            bool isNew = false;

            ProjectData.ProjectRow row = _projectData.Project.FirstOrDefault();
            if (row == null)
            {
                row = _projectData.Project.NewProjectRow();

                isNew = true;
            }

            row.ConnectionString = View.SelectedConnectionString;
            row.UseMSSpaceUsed = View.UseMSSpaceUsed;
            row.PercentageReserveForIndexes = View.PercentageReserveForDataAndIndexes.Value;

            if (isNew)
            {
                _projectData.Project.AddProjectRow(row);
            }
        }
    }
}
